package cn.edu.swu.bc.utils;

import cn.edu.swu.bc.entity.Administrator;
import cn.edu.swu.bc.entity.Blog;
import cn.edu.swu.bc.entity.User;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class DBUtils {
    private static Context ctx;
    private static String DSNAME = "java:comp/env/jdbc/connPool";

    //获取连继池中的连接
    private static Connection getConnection2() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = null;
        try {
            ctx = new InitialContext();
            comboPooledDataSource = (ComboPooledDataSource) ctx.lookup(DSNAME);
        } catch (NamingException e) {
            e.printStackTrace();
        }
        if (comboPooledDataSource != null)
            return comboPooledDataSource.getConnection();
        else
            return null;
    }

    //返回连接
    private static void closeConnection(Statement statement, Connection connection) {
        try {
            statement.close();
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void insert(String sql) throws SQLException {
        excute(sql);
    }

    public static void delete(String sql) throws SQLException {
        excute(sql);
    }

    public static void update(String sql) throws SQLException {
        excute(sql);
    }

    private static void excute(String sql) throws SQLException {


        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                statement.execute(sql);
            }
        }
    }

    //获得用户信息
    public static User GetUser(int ID) throws SQLException {
        String sql = "select * from users where uid='" + ID + "'";
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                User user = new User();
                if (rs.next()) {
                    // uid | uname | upassword | avatar | nickname | birthday | location | sex  | signature |
                    user.setID(rs.getInt("uid"));
                    user.setNikcname(rs.getString("nickname"));
                    user.setUsername(rs.getString("uname"));
                    user.setPassword(rs.getString("upassword"));
                    user.setAvatar(rs.getString("avatar"));
                    Date date = rs.getDate("birthday");
                    if (date != null)
                        user.setBirthday(date.toString());
                    else user.setBirthday(null);
                    user.setSex(rs.getString("sex"));
                    user.setSignature(rs.getString("signature"));
                    user.setLocation(rs.getString("location"));
                }
                closeConnection(statement, connection);
                return user;
            }
        }
    }

    //通过用户名获得用户信息
    public static User GetUser(String name) throws SQLException {
        String sql = "select * from users where uname='" + name + "'";
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                User user = new User();
                if (rs.next()) {
                    // uid | uname | upassword | avatar | nickname | birthday | location | sex  | signature |
                    user.setID(rs.getInt("uid"));
                    user.setNikcname(rs.getString("nickname"));
                    user.setUsername(rs.getString("uname"));
                    user.setPassword(rs.getString("upassword"));
                    user.setAvatar(rs.getString("avatar"));
                    Date date = rs.getDate("birthday");
                    if (date != null)
                        user.setBirthday(date.toString());
                    else user.setBirthday(null);
                    user.setSex(rs.getString("sex"));
                    user.setSignature(rs.getString("signature"));
                    user.setLocation(rs.getString("location"));
                }
                closeConnection(statement, connection);
                return user;
            }
        }
    }

    //获取管理员
    public static Administrator GetAdmin(String name) throws SQLException {
        String sql = "select * from admins where name='" + name + "'";
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                Administrator admin = new Administrator();
                if (rs.next()) {
                    // id | name | password
                    admin.setAdminName(rs.getString("name"));
                    admin.setID(rs.getInt("id"));
                    admin.setPassword(rs.getString("password"));
                }
                closeConnection(statement, connection);
                return admin;
            }
        }
    }

    //获取关注的用户的ID
    public static String GetFriendsID(int id) throws SQLException {
        String sql = " select * from friends where id=" + id;
        StringBuilder fids = new StringBuilder();
        fids.append("(");
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    fids.append(rs.getInt("fid") + ",");
                }
                fids.replace(fids.length() - 1, fids.length(), ")");
                closeConnection(statement, connection);
                return fids.toString();
            }
        }
    }

    //获取指定页面博客的信息
    public static List<Blog> getBlogs(int uid, int page) throws SQLException {

        int offset = (page - 1) * 5;
        String sql ;
        //三表联立的sql语句，看起来比较复杂
        if (uid == 0)
            sql = "select * from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                    "left join blogs on blogs.id= `users-blogs`.bid where  `users-blogs`.bid " +
                    "is not null order by id desc limit 5 offset " + offset;
        else
            sql = "select * from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                    "left join blogs on blogs.id= `users-blogs`.bid where `users-blogs`.uid= " + uid +
                    " order by id desc limit 5 offset " + offset;
        List<Blog> blogs = new ArrayList<Blog>();
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    //构造博客类
                    Blog blog = new Blog();
                    blog.setAuthorname(rs.getString("nickname"));
                    blog.setID(rs.getInt("bid"));
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String date = simpleDateFormat.format(rs.getTimestamp("createtime"));
                    blog.setDatetime(date);
                    blog.setUID(rs.getInt("UID"));
                    blog.setTitle(rs.getString("title"));
                    String pics = rs.getString("pics");
                    if (pics != null) {
                        String pic = pics.split(",")[0];
                        blog.setPic(pic);
                    } else blog.setPic(null);
                    blog.setAuthorAvatar(rs.getString("avatar"));
                    blogs.add(blog);
                }
                closeConnection(statement, connection);
            }
        }
        return blogs;
    }
    //获取关注的用户的博客
    public static List<Blog> getFriendsBlogs(int uid, int page) throws SQLException {
        String fids = DBUtils.GetFriendsID(uid);
        int offset = (page - 1) * 5;
        String sql = "select * from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                "left join blogs on blogs.id= `users-blogs`.bid where  `users-blogs`.uid in " + fids +
                " order by id desc limit 5 offset " + offset;
        List<Blog> blogs = new ArrayList<Blog>();
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    Blog blog = new Blog();
                    blog.setAuthorname(rs.getString("uname"));
                    blog.setID(rs.getInt("bid"));
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String date = simpleDateFormat.format(rs.getTimestamp("createtime"));
                    blog.setDatetime(date);
                    blog.setUID(rs.getInt("UID"));
                    blog.setTitle(rs.getString("title"));
                    String pics = rs.getString("pics");
                    if (pics != null) {
                        String pic = pics.split(",")[0];
                        blog.setPic(pic);
                    } else blog.setPic(null);
                    blog.setAuthorAvatar(rs.getString("avatar"));
                    blogs.add(blog);
                }
                closeConnection(statement, connection);
            }
        }
        return blogs;
    }


    //获取博客的总数量
    public static int getBlogsNumber() throws SQLException {
        String sql = "select count(*) from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                "left join blogs on blogs.id= `users-blogs`.bid where  `users-blogs`.bid is not null ";
        int blogsNumber = 0;
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next())
                    blogsNumber = rs.getInt(1);
            }
        }
        return blogsNumber;
    }

    //通过用户id获取博客数量
    public static int getBlogsNumber(int uid) throws SQLException {
        String sql = "select count(*) from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                "left join blogs on blogs.id= `users-blogs`.bid where  `users-blogs`.uid =" + uid;
        int blogsNumber = 0;
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next())
                    blogsNumber = rs.getInt(1);
                closeConnection(statement, connection);
            }
        }
        return blogsNumber;
    }

    //通过用户id的字符串获取博客数量，字符串可以包涵多个用户对的id
    public static int getBlogsNumber(String uid) throws SQLException {
        String sql = "select count(*) from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                "left join blogs on blogs.id= `users-blogs`.bid where  `users-blogs`.uid in" + uid;
        int blogsNumber = 0;
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next())
                    blogsNumber = rs.getInt(1);
                closeConnection(statement, connection);
            }
        }
        return blogsNumber;
    }

    //得到最后插入的博客的文件名
    public static String getInsertBlogFileName() throws SQLException {
        String sql = "SELECT MAX(ID) FROM  BLOGS;";
        String filename = null;
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    filename = Integer.toString(rs.getInt(1));
                }
                closeConnection(statement, connection);
            }
            return filename;
        }
    }

    //获取用户注册的id
    public static Integer getRigsterUserID() throws SQLException {
        String sql = "SELECT MAX(UID) FROM  users;";
        int id = 0;
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    id = rs.getInt(1);
                }
                closeConnection(statement, connection);
            }
            return id;
        }
    }

    //获取博客的信息
    public static Blog getBlogInfoByID(int BID) throws SQLException {
        String sql = "select * from (users left join `users-blogs` on users.uid=`users-blogs`.uid) " +
                "left join blogs on blogs.id= `users-blogs`.bid where  `users-blogs`.bid=" + BID;
        Blog blog = new Blog();
        try (Connection connection = getConnection2()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    //构造博客类
                    blog.setPic(rs.getString("pics"));
                    blog.setPic(rs.getString("pics"));
                    blog.setTitle(rs.getString("title"));
                    blog.setAuthorname(rs.getString("nickname"));
                    blog.setUID(rs.getInt("uid"));
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String date = simpleDateFormat.format(rs.getTimestamp("createtime"));
                    blog.setDatetime(date);
                }
                closeConnection(statement, connection);
            }
            return blog;
        }
    }
}
